#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 文件名：14-mysql操作类.py

'''通过导入时间模块，休眠5秒，可以看到是否自动关闭连接
import time
time.sleep(5)
db.close()
'''

import MySQLdb
import time
import re


class MysqldbHelper(object):
    """操作mysql数据库，基本方法

    	"""

    def __init__(self, host="127.0.0.1", username="jygsuser", password="Aa12345678", port=3306, database="ops", charset="utf8"):
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.port = port
        self.con = None
        self.cur = None
        try:
            self.con = MySQLdb.connect(host=self.host, user=self.username, passwd=self.password, port=self.port, db=self.database, charset="utf8")
            # 所有的查询，都在连接 con 的一个模块 cursor 上面运行的
            self.cur = self.con.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            self.cur.execute('SET NAMES UTF8')
        except:
            raise "DataBase connect error,please check the db config."

    def close(self):
        """关闭数据库连接

        """
        if not self.con:
            self.con.close()
            print "关闭数据库连接"
        else:
            raise "DataBase doesn't connect,close connectiong error;please check the db config."

    def getVersion(self):
        """获取数据库的版本号

        """
        self.cur.execute("SELECT VERSION()")
        time.sleep(5)
        return self.getOneData()

    def getOneData(self):
        # 取得上个查询的结果，是单个结果
        data = self.cur.fetchone()
        #{'VERSION()': u'5.6.40-log'}
        return data

    def creatTable(self, tablename, attrdict, constraint):
        """创建数据库表

        	args：
        		tablename  ：表名字
        		attrdict   ：属性键值对,{'book_name':'varchar(200) NOT NULL'...}
        		constraint ：主外键约束,PRIMARY KEY(`id`)
        """
        if self.isExistTable(tablename):
            print "{0} 存在 ".format(tablename)
            return
        sql = ''
        sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
        for attr, value in attrdict.items():
            sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','
        sql = sql + 'CREATE TABLE IF NOT EXISTS {0} ('.format(tablename)
        sql = sql + sql_mid
        sql = sql + constraint
        sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
        # print 'creatTable:' + sql
        self.executeCommit(sql)

    def isExistTable(self, tablename):
        """判断数据表是否存在

        	args：
        		tablename  ：表名字

        	Return:
        		存在返回True，不存在返回False
        """
        sql = "select * from %s" % tablename
        result = self.executeCommit(sql)
        if result is None:
            print "{0} 存在 ".format(tablename)
            return True
        else:
            if re.search("doesn't exist", result):
                print "{0} 不存在 ".format(tablename)
                return False
            else:
                print "{0} 存在 ，不清楚何种场合会报出这个信息".format(tablename)
                return True

    def insert(self, tablename, params):
        """创建数据库表

        	args：
                    tablename  ：表名字
                    key        ：属性键
                    value      ：属性值
        """
        key = []
        value = []
        for tmpkey, tmpvalue in params.items():
            key.append(tmpkey)

            if isinstance(tmpvalue, str):
                value.append("'" + tmpvalue + "'")
            else:
                value.append(str(tmpvalue))
                     
        attrs_sql = '(' + ','.join(key) + ')'
        values_sql = ' values(' + ','.join(value) + ')'
        
        sql = 'insert into %s'%tablename
        sql = sql + attrs_sql + values_sql
        
        print '===插入的SQL语句： ' + sql + '==='
        self.executeCommit(sql)

    def executeCommit(self, sql=''):
        """执行数据库sql语句，针对更新,删除,事务等操作失败时回滚

        """
        try:
            self.cur.execute(sql)
            self.con.commit()
            print " ===执行=== \n {0} \n === OK ===".format(sql)
        except MySQLdb.Error, e:
            self.con.rollback()
            error = 'MySQL execute failed! ERROR {0}: {1}'.format(e.args[0], e.args[1])
            # print "error:", error
            return error


if __name__ == "__main__":
    mydb = MysqldbHelper()
    print mydb.getVersion()

    table = 'test_mysqldb'
    attrs = {'name': 'varchar(200) DEFAULT NULL', 'age': 'int(11) DEFAULT NULL', 'income': 'int(11)', 'sex': "enum('m','w') DEFAULT 'm'"}
    constraint = 'PRIMARY KEY(`id`)'

    mydb.creatTable(table, attrs, constraint)

    params = {"name": "caixinglong2", "age": "11",  "income": 3300,"sex": 'm', }
    mydb.insert('test_mysqldb', params)


'''
数据库添加字段
"sex": 'm',
'update_time': '2019-08-06 15:17:13'
'income': 'int(11)','sex': 'enum('m','w')DEFAULT 'm''

整形
alter table test_mysqldb add income int(10) ;
枚举
alter table test_mysqldb add sex enum('m','w')DEFAULT 'm' ;
时间段
alter table test_mysqldb add update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

'''
